﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace VitCode.Castle.Helper
{
    public class CsvHelper
    {
        public OleDbConnection OleCon = new OleDbConnection();
        public OleDbCommand OleCmd = new OleDbCommand();
        public OleDbDataAdapter OleDa = new OleDbDataAdapter();
        #region 讀取Csv文件返回DataSet
        /// <summary>   
        /// 讀取Csv文件返回DataSet   
        /// </summary>   
        /// <param name="filePath">檔路徑 </param>   
        /// <returns>Csv內容</returns>          
        public DataSet CsvReader(string filePath)
        {
            DataSet CsvData = new DataSet();
            var dirPath = filePath.Substring(0, filePath.LastIndexOf('\\'));
            OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirPath + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
            OleCon.Open();
            OleCmd.Connection = OleCon;
            var fileName = filePath.Substring(filePath.LastIndexOf('\\') + 1);
            var sql = "Select * from ["+fileName+"]";
            OleCmd.CommandText = sql;
            OleDa.SelectCommand = OleCmd;
            try
            {
                OleDa.Fill(CsvData, "Csv");
                return CsvData;
            }
            //catch(Exception ex)
            //{
                
            //    return CsvData;
            //}
            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }

        }
        #endregion

        public DataTable GetDataTableFromCsvFile(string filePath,char chFg)
        {
            var dt = new DataTable();
            var sr = new StreamReader(filePath);
            try
            {

                var str = sr.ReadLine();
                if(!string.IsNullOrEmpty(str))
                {
                    var ss = str.Split(chFg);
                    for(var i=0;i<ss.Length;i++)
                    {
                        dt.Columns.Add(ss[i], typeof (String));
                    }
                }

                while (sr.Peek() > 0)
                {
                    str = sr.ReadLine();
                    if(!string.IsNullOrEmpty(str))
                    {
                        var ss = str.Split(chFg);
                        dt.Rows.Add(ss);
                    }
                }
            }
            finally
            {
                sr.Close();
                sr.Dispose();
            }
            return dt;
        }
    }  

}
